In [1]:
import pymongo
from pymongo import MongoClient
import time
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import csv
In [2]:
Client = MongoClient("mongodb://bridges:readonly@nbi-mongo.admin/bridge")
db = Client.bridge
collection = db["bridges"]
For demonstration purposes, the results only focuses on the states in the Midwestern United States which includes: Illinois, Indiana, Iowa, Kansas, Michigan, Minnesota, Missouri, Nebraska, North Dakota, Ohio, South Dakota, and Wisconsin.
Reference : http://soileiragusgonta.com/
The classification of the bridge into slow deteriorating, fast deteriorating, and average deteriorating is done based on bridge's rate of deterioration. Therefore, In this section will demonstrate how bridges deteriorate over time in the Midwestern United States. To plot the deterioration curve of bridges in every state of Midwestern United States, bridges were grouped by their age. As a result, There are 60 groups of bridges from age 1 to 60, The mean of the condition rating of the deck, superstructure, and substructure of the bridge is plotted for every age.
In [ ]:
def getData(state):
pipeline = [{"$match":{"$and":[{"year":{"$gt":2015, "$lt":2017}},{"stateCode":state}]}},
{"$project":{"_id":0,
"stateCode":1,
"structureNumber":1,
"yearBuilt":1,
"deck":1, ## rating of deck
"year":1, ## survey year
"substructure":1, ## rating of substructure
"superstructure":1, ## rating of superstructure
}}]
dec = collection.aggregate(pipeline)
conditionRatings = pd.DataFrame(list(dec))
conditionRatings['Age'] = conditionRatings['year'] - conditionRatings['yearBuilt']
return conditionRatings
After NBI data is extracted. The Data has to be filtered to remove data points with missing values such as 'N', 'NA'. The mean condition rating for all the components: Deck, Substructure, and Superstructe, has to be calculated.
In [4]:
def getMeanRatings(state,startAge, endAge, startYear, endYear):
conditionRatings = getData(state)
conditionRatings = conditionRatings[['structureNumber','Age','superstructure','deck','substructure','year']]
conditionRatings = conditionRatings.loc[~conditionRatings['superstructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['substructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['deck'].isin(['N','NA'])]
#conditionRatings = conditionRatings.loc[~conditionRatings['Structure Type'].isin([19])]
#conditionRatings = conditionRatings.loc[~conditionRatings['Type of Wearing Surface'].isin(['6'])]
maxAge = conditionRatings['Age'].unique()
tempConditionRatingsDataFrame = conditionRatings.loc[conditionRatings['year'].isin([i for i in range(startYear, endYear+1, 1)])]
MeanDeck = []
StdDeck = []
MeanSubstructure = []
StdSubstructure = []
MeanSuperstructure = []
StdSuperstructure = []
## start point of the age to be = 1 and ending point = 100
for age in range(startAge,endAge+1,1):
## Select all the bridges from with age = i
tempAgeDf = tempConditionRatingsDataFrame.loc[tempConditionRatingsDataFrame['Age'] == age]
## type conversion deck rating into int
listOfMeanDeckOfAge = list(tempAgeDf['deck'])
listOfMeanDeckOfAge = [ int(deck) for deck in listOfMeanDeckOfAge ]
## takeing mean and standard deviation of deck rating at age i
meanDeck = np.mean(listOfMeanDeckOfAge)
stdDeck = np.std(listOfMeanDeckOfAge)
## type conversion substructure rating into int
listOfMeanSubstructureOfAge = list(tempAgeDf['substructure'])
listOfMeanSubstructureOfAge = [ int(substructure) for substructure in listOfMeanSubstructureOfAge ]
meanSub = np.mean(listOfMeanSubstructureOfAge)
stdSub = np.std(listOfMeanSubstructureOfAge)
## type conversion substructure rating into int
listOfMeanSuperstructureOfAge = list(tempAgeDf['superstructure'])
listOfMeanSuperstructureOfAge = [ int(superstructure) for superstructure in listOfMeanSuperstructureOfAge ]
meanSup = np.mean(listOfMeanSuperstructureOfAge)
stdSup = np.std(listOfMeanSuperstructureOfAge)
#Append Deck
MeanDeck.append(meanDeck)
StdDeck.append(stdDeck)
#Append Substructure
MeanSubstructure.append(meanSub)
StdSubstructure.append(stdSub)
#Append Superstructure
MeanSuperstructure.append(meanSup)
StdSuperstructure.append(stdSup)
return [MeanDeck, StdDeck ,MeanSubstructure, StdSubstructure, MeanSuperstructure, StdSuperstructure]
The calculated Mean Condition Ratings of deck, superstructure, and substructure are now stored in seperate dataframe for the convience.
In [ ]:
states = ['31','19','17','18','20','26','27','29','38','46','39','55']
# state code to state abbreviation
stateNameDict = {'25':'MA',
'04':'AZ',
'08':'CO',
'38':'ND',
'09':'CT',
'19':'IA',
'26':'MI',
'48':'TX',
'35':'NM',
'17':'IL',
'51':'VA',
'23':'ME',
'16':'ID',
'36':'NY',
'56':'WY',
'29':'MO',
'39':'OH',
'28':'MS',
'11':'DC',
'21':'KY',
'18':'IN',
'06':'CA',
'47':'TN',
'12':'FL',
'24':'MD',
'34':'NJ',
'46':'SD',
'13':'GA',
'55':'WI',
'30':'MT',
'54':'WV',
'15':'HI',
'32':'NV',
'37':'NC',
'10':'DE',
'33':'NH',
'44':'RI',
'50':'VT',
'42':'PA',
'05':'AR',
'20':'KS',
'45':'SC',
'22':'LA',
'40':'OK',
'72':'PR',
'41':'OR',
'27':'MN',
'53':'WA',
'01':'AL',
'31':'NE',
'02':'AK',
'49':'UT'
}
def getBulkMeanRatings(states, stateNameDict):
# Initializaing the dataframes for deck, superstructure and subtructure
df_mean_deck = pd.DataFrame({'Age':range(1,61)})
df_mean_sup = pd.DataFrame({'Age':range(1,61)})
df_mean_sub = pd.DataFrame({'Age':range(1,61)})
df_std_deck = pd.DataFrame({'Age':range(1,61)})
df_std_sup = pd.DataFrame({'Age':range(1,61)})
df_std_sub = pd.DataFrame({'Age':range(1,61)})
for state in states:
meanDeck, stdDeck, meanSub, stdSub, meanSup, stdSup = getMeanRatings(state,1,100,1992,2016)
stateName = stateNameDict[state]
print('State Name: ',stateName)
df_mean_deck[stateName] = meanDeck[:60]
df_mean_sup[stateName] = meanSup[:60]
df_mean_sub[stateName] = meanSub[:60]
df_std_deck[stateName] = stdDeck[:60]
df_std_sup[stateName] = stdSup[:60]
df_std_sub[stateName] = stdSub[:60]
return df_mean_deck, df_mean_sup, df_mean_sub, df_std_deck, df_std_sup, df_std_sub
df_mean_deck, df_mean_sup, df_mean_sub, df_std_deck, df_std_sup, df_std_sub = getBulkMeanRatings(states, stateNameDict)
In [ ]:
df_mean_deck.to_csv['deckmidwestern.csv']
The figure below the deterioration curve of mean deck rating of twelve midwestern states in united states. All of the states have similar deterioration curves. Most of the bridges when first built are given a condition rating of 8 or 9. Michigan's (MI) deterioration curve is anomaly as it starts with a mean condition rating of approximately 7.2.
The following charts presents two different perspective to look at deterioration of bridges in midwestern united states:
In [9]:
%matplotlib inline
states = ['31','19','17','18','20','26','27','29','38','46','39','55']
palette = [ 'blue', 'blue', 'green', 'magenta', 'cyan', 'brown', 'grey',
'red','silver','purple', 'gold', 'black','olive' ]
plt.figure(figsize = (10,8))
index = 0
for state in states:
index = index + 1
stateName = stateNameDict[state]
plt.plot(df_mean_deck['Age'],df_mean_deck[stateName], color = palette[index])
plt.legend([stateNameDict[state] for state in states],loc='upper right', ncol = 2)
plt.xlim(1,60)
plt.ylim(1,9)
plt.title('Mean Deck Rating Vs Age')
plt.xlabel('Age')
plt.ylabel('Mean Deck Rating')
Out[9]:
In [107]:
plt.figure(figsize = (16,12))
plt.xlabel('Age')
plt.ylabel('Mean')
# Initialize the figure
plt.style.use('seaborn-darkgrid')
# create a color palette
#palette = plt.get_cmap('gist_ncar')
palette = [
'blue', 'blue', 'green','magenta','cyan','brown','grey','red','silver','purple','gold','black','olive'
]
# multiple line plot
num=1
for column in df_mean_deck.drop('Age', axis=1):
# Find the right spot on the plot
plt.subplot(4,3, num)
# Plot the lineplot
plt.plot(df_mean_deck['Age'], df_mean_deck[column], marker='', color=palette[num], linewidth=4, alpha=0.9, label=column)
# Same limits for everybody!
plt.xlim(1,60)
plt.ylim(1,9)
# Not ticks everywhere
if num in range(10) :
plt.tick_params(labelbottom='off')
if num not in [1,4,7,10]:
plt.tick_params(labelleft='off')
# Add title
plt.title(column, loc='left', fontsize=12, fontweight=0, color=palette[num])
plt.text(30, -1, 'Age', ha='center', va='center')
plt.text(1, 4, 'Mean Deck Rating', ha='center', va='center', rotation='vertical')
num = num + 1
# general title
plt.suptitle("Mean Deck Rating vs Age \nIndividual State Deterioration Curves", fontsize=13, fontweight=0, color='black', style='italic', y=1.02)
Out[107]:
In [25]:
In [16]:
df_mean_deck
Out[16]:
The figure below the deterioration curve of mean Superstructure rating of twelve midwestern states in united states. All of the states have similar deterioration for all superstructure, substructure, and deck curves. This provides a hint that the components of the bridge such as superstructure, subtructure, and deck are highly correlated. Most of the bridges when first built are given a condition rating of 8 or 9. Michigan's (MI) deterioration curve is anomaly as it starts with a mean condition rating of approximately 7.2.
The following charts presents similar perspective to look at deterioration of bridges in midwestern united states, like we have looked into deterioration deck.
In [19]:
states = ['31','19','17','18','20','26','27','29','38','46','39','55']
palette = [ 'blue', 'blue', 'green', 'magenta', 'cyan', 'brown', 'grey',
'red','silver','purple', 'gold', 'black','olive' ]
plt.figure(figsize = (10,8))
index = 0
for state in states:
index = index + 1
stateName = stateNameDict[state]
plt.plot(df_mean_sup['Age'],df_mean_sup[stateName], color = palette[index])
plt.legend([stateNameDict[state] for state in states],loc='upper right', ncol = 2)
plt.xlim(1,60)
plt.ylim(1,9)
plt.title('Mean Superstructure Rating Vs Age')
plt.xlabel('Age')
plt.ylabel('Mean Superstructure Rating')
Out[19]:
In [106]:
plt.figure(figsize = (16,12))
plt.xlabel('Age')
plt.ylabel('Mean')
# Initialize the figure
plt.style.use('seaborn-darkgrid')
# create a color palette
#palette = plt.get_cmap('gist_ncar')
palette = [
'blue',
'blue',
'green',
'magenta',
'cyan',
'brown',
'grey',
'red',
'silver',
'purple',
'gold',
'black',
'olive'
]
# multiple line plot
num=1
for column in df_mean_sup.drop('Age', axis=1):
# Find the right spot on the plot
plt.subplot(4,3, num)
# Plot the lineplot
plt.plot(df_mean_sup['Age'], df_mean_sup[column], marker='', color=palette[num], linewidth=4, alpha=0.9, label=column)
# Same limits for everybody!
plt.xlim(1,60)
plt.ylim(1,9)
# Not ticks everywhere
if num in range(10) :
plt.tick_params(labelbottom='off')
if num not in [1,4,7,10]:
plt.tick_params(labelleft='off')
# Add title
plt.title(column, loc='left', fontsize=12, fontweight=0, color=palette[num])
plt.text(30, -1, 'Age', ha='center', va='center')
plt.text(1, 4, 'Mean Superstructure Rating', ha='center', va='center', rotation='vertical')
num = num + 1
# general title
plt.suptitle("Mean Superstructure Rating vs Age \nIndividual State Deterioration Curves", fontsize=13, fontweight=0, color='black', style='italic', y=1.02)
Out[106]:
In [42]:
states = ['31','19','17','18','20','26','27','29','38','46','39','55']
palette = [ 'blue', 'blue', 'green', 'magenta', 'cyan', 'brown', 'grey',
'red','silver','purple', 'gold', 'black','olive' ]
plt.figure(figsize = (10,8))
index = 0
for state in states:
index = index + 1
stateName = stateNameDict[state]
plt.plot(df_mean_sub['Age'],df_mean_sub[stateName], color = palette[index], linewidth=4)
plt.legend([stateNameDict[state] for state in states],loc='upper right', ncol = 2)
plt.xlim(1,60)
plt.ylim(1,9)
plt.title('Mean Substructure Rating Vs Age')
plt.xlabel('Age')
plt.ylabel('Mean Substructure Rating')
Out[42]:
In [43]:
plt.figure(figsize = (16,12))
plt.xlabel('Age')
plt.ylabel('Mean')
# Initialize the figure
plt.style.use('seaborn-darkgrid')
# create a color palette
palette = [
'blue', 'blue', 'green', 'magenta', 'cyan', 'brown', 'grey', 'red', 'silver', 'purple', 'gold', 'black','olive'
]
# multiple line plot
num=1
for column in df_mean_sub.drop('Age', axis=1):
# Find the right spot on the plot
plt.subplot(4,3, num)
# Plot the lineplot
plt.plot(df_mean_sub['Age'], df_mean_sub[column], marker='', color=palette[num], linewidth=4, alpha=0.9, label=column)
# Same limits for everybody!
plt.xlim(1,60)
plt.ylim(1,9)
# Not ticks everywhere
if num in range(7) :
plt.tick_params(labelbottom='off')
if num not in [1,4,7] :
plt.tick_params(labelleft='off')
# Add title
plt.title(column, loc='left', fontsize=12, fontweight=0, color=palette[num])
plt.text(30, -1, 'Age', ha='center', va='center')
plt.text(1, 4, 'Mean Substructure Rating', ha='center', va='center', rotation='vertical')
num = num + 1
# general title
plt.suptitle("Mean Substructure Rating vs Age \nIndividual State Deterioration Curves", fontsize=13, fontweight=0, color='black', style='italic', y=1.02)
Out[43]:
In [84]:
def getDataOneYear(state):
pipeline = [{"$match":{"$and":[{"year":{"$gt":2015, "$lt":2017}},{"stateCode":state}]}},
{"$project":{"_id":0,
"structureNumber":1,
"yearBuilt":1,
"deck":1, ## rating of deck
"year":1, ## survey year
"substructure":1, ## rating of substructure
"superstructure":1, ## rating of superstructure
}}]
dec = collection.aggregate(pipeline)
conditionRatings = pd.DataFrame(list(dec))
conditionRatings['Age'] = conditionRatings['year'] - conditionRatings['yearBuilt']
return conditionRatings
The mean deterioration curve can be a measure to evaluate the rate of deterioration. If the condition rating of a bridge lies above the deterioration curve then the bridge is deteriorating at a slower pace than mean deterioration of the bridges, and if the condition rating of the bridge lies below the deterioration curve of the bridges then it is deteriorating at a faster pace than the mean deterioration of the bridges.
This concept can further be extended to calculate deterioration score. Deterioration score denotes the rate of deterioration. A positive deterioration denotes that the individual bridge is deteriorating at a slower pace than the mean rate of deterioration of bridges, and a negative deterioration denotes that the individual bridge is deteriorating at a higher pace than the mean deterioration of the bridges.
The following provides definition of deterioration score:
The classfication criteria used to classify bridges into slow Deterioration, average deterioration and fast deterioration. Bridges are classified based on how far an individual bridge’s deterioration score is from the mean deterioration score.
| Categories | Value |
|---|---|
| Slow Deterioration | $z_ia$ ≥ $\bar x_a$ + 1 σ ( $ x_a$ ) |
| Average Deterioration | $\bar x_a$ - 1 σ ( $x_a$ ) ≥ $z_ia$ ≥ $\bar x_a$ + 1 σ ( $ x_a$ ) |
| Fast Deterioration | $z_ia$ ≤ $\bar x_a$ - 1 σ ( $ x_a$ ) |
In [103]:
stat = ['31','19','17','18','20','26','27','29','38','46','39','55']
AgeList = list(conditionRatings['Age'])
deckList = list(conditionRatings['deck'])
num = 1
for st in stat:
deckR = []
deckR = getDataOneYear(st)
deckR = deckR[['Age','deck']]
deckR= deckR.loc[~deckR['deck'].isin(['N','NA'])]
stateName = stateNameDict[st]
labels = []
for deckRating, Age in zip (deckList,AgeList):
if Age < 60:
mean_age_conditionRating = df_mean_deck[stateName][Age]
std_age_conditionRating = df_std_deck[stateName][Age]
detScore = (int(deckRating) - mean_age_conditionRating) / std_age_conditionRating
if (mean_age_conditionRating - std_age_conditionRating) < int(deckRating) <= (mean_age_conditionRating + std_age_conditionRating):
# Append a label
labels.append('Average Deterioration')
# else, if more than a value,
elif int(deckRating) > (mean_age_conditionRating + std_age_conditionRating):
# Append a label
labels.append('Slow Deterioration')
# else, if more than a value,
elif int(deckRating) < (mean_age_conditionRating - std_age_conditionRating):
# Append a label
labels.append('Fast Deterioration')
else:
labels.append('Null Value')
D = dict((x,labels.count(x)) for x in set(labels))
plt.figure(figsize=(12,6))
plt.title(stateName)
plt.bar(range(len(D)), list(D.values()), align='center')
plt.xticks(range(len(D)), list(D.keys()))
plt.xlabel('Categories')
plt.ylabel('Number of Bridges')
plt.show()
num = num + 1
In [ ]:
stat = ['31','19','17','18','20','26','27','29','38','46','39','55']
AgeList = list(conditionRatings['Age'])
deckList = list(conditionRatings['deck'])
num = 1
label = []
for st in stat:
deckR = []
deckR = getDataOneYear(st)
deckR = deckR[['Age','deck']]
deckR= deckR.loc[~deckR['deck'].isin(['N','NA'])]
stateName = stateNameDict[st]
for deckRating, Age in zip (deckList,AgeList):
if Age < 60:
mean_age_conditionRating = df_mean_deck[stateName][Age]
std_age_conditionRating = df_std_deck[stateName][Age]
detScore = (int(deckRating) - mean_age_conditionRating) / std_age_conditionRating
if (mean_age_conditionRating - std_age_conditionRating) < int(deckRating) <= (mean_age_conditionRating + std_age_conditionRating):
# Append a label
labels.append('Average Deterioration')
# else, if more than a value,
elif int(deckRating) > (mean_age_conditionRating + std_age_conditionRating):
# Append a label
labels.append('Slow Deterioration')
# else, if more than a value,
elif int(deckRating) < (mean_age_conditionRating - std_age_conditionRating):
# Append a label
labels.append('Fast Deterioration')
else:
labels.append('Null Value')
In [100]:
D = dict((x,labels.count(x)) for x in set(labels))
plt.figure(figsize=(12,6))
plt.title('Classification of Bridges in Midwestern United States')
plt.bar(range(len(D)), list(D.values()), align='center')
plt.xticks(range(len(D)), list(D.keys()))
plt.xlabel('Categories of Bridges')
plt.ylabel('Number of Bridges')
plt.show()
In [7]:
st = getData('31')
In [8]:
st
Out[8]:
In [42]:
def getData(state):
pipeline = [{"$match":{"$and":[{"year":{"$gt":2015, "$lt":2017}},{"stateCode":state}]}},
{"$project":{"_id":0,
"stateCode":1,
"structureNumber":1,
"yearBuilt":1,
"deck":1, ## rating of deck
"year":1, ## survey year
"substructure":1, ## rating of substructure
"superstructure":1, ## rating of superstructure
}}]
dec = collection.aggregate(pipeline)
conditionRatings = pd.DataFrame(list(dec))
conditionRatings['Age'] = conditionRatings['year'] - conditionRatings['yearBuilt']
return conditionRatings
conditionRatings = getData('31')
conditionRatings = conditionRatings[['structureNumber','Age','superstructure','deck','substructure','year']]
conditionRatings = conditionRatings.loc[~conditionRatings['superstructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['substructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['deck'].isin(['N','NA'])]
In [51]:
## state, total bridges, PSD, PFD, PAD, Mean Deck, Mean Substructure, Mean Superstructure, Text
dict_state_info = {}
stateNameDict = {'25':'MA',
'04':'AZ',
'08':'CO',
'38':'ND',
'09':'CT',
'19':'IA',
'26':'MI',
'48':'TX',
'35':'NM',
'17':'IL',
'51':'VA',
'23':'ME',
'16':'ID',
'36':'NY',
'56':'WY',
'29':'MO',
'39':'OH',
'28':'MS',
'11':'DC',
'21':'KY',
'18':'IN',
'06':'CA',
'47':'TN',
'12':'FL',
'24':'MD',
'34':'NJ',
'46':'SD',
'13':'GA',
'55':'WI',
'30':'MT',
'54':'WV',
'15':'HI',
'32':'NV',
'37':'NC',
'10':'DE',
'33':'NH',
'44':'RI',
'50':'VT',
'42':'PA',
'05':'AR',
'20':'KS',
'45':'SC',
'22':'LA',
'40':'OK',
'72':'PR',
'41':'OR',
'27':'MN',
'53':'WA',
'01':'AL',
'31':'NE',
'02':'AK',
'49':'UT'
}
stateAbbFull = {'MA':'Massachusetts',
'AZ':'Arizona',
'CO':'Colorado',
'ND':'North Dakota',
'CT':'Connecticut',
'IA':'Iowa',
'MI':'Michigan',
'TX':'Texas',
'NM':'New Mexico',
'IL':'Illinois',
'VA':'Virginia',
'ME':'Maine',
'ID':'Idaho',
'NY':'New York',
'WY':'Wyoming',
'MO':'Missouri',
'OH':'Ohio',
'MS':'Mississip[i',
'DC':'Washington DC',
'KY':'Kentucky',
'IN':'Indiana',
'CA':'California',
'TN':'Tennessee',
'FL':'Florida',
'MD':'Maryland',
'NJ':'New Jersey',
'SD':'South Dakota',
'GA':'Georgia',
'WI':'Wisconsin',
'MT':'Montana',
'WV':'West Virginia',
'HI':'Hawaii',
'NV':'Nevada',
'NC':'North Carolina',
'DE':'Delaware',
'NH':'New Hampshire ',
'RI':'Rhode Island',
'VT':'Vermont',
'PA':'Pennsylvania',
'AR':'Arkansas',
'KS':'Kansas',
'SC':'South Carolina',
'LA':'Louisiana',
'OK':'Oklahoma',
'PR':'Puerto Rico',
'OR':'Oregon',
'MN':'Minnesota',
'WA':'Washington',
'AL':'Alabama',
'NE':'Nebraska',
'AK':'Alaska',
'UT':'Utah'
}
code = []
name = []
nob = []
sup = []
sub = []
deck = []
for state in states:
conditionRatings = getData(state)
conditionRatings = conditionRatings[['structureNumber','Age','superstructure','deck','substructure','year']]
conditionRatings = conditionRatings.loc[~conditionRatings['superstructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['substructure'].isin(['N','NA'])]
conditionRatings = conditionRatings.loc[~conditionRatings['deck'].isin(['N','NA'])]
code.append(stateNameDict[state])
name.append(stateAbbFull[stateNameDict[state]])
nob.append(len(conditionRatings))
sub.append(np.mean([int(j) for j in conditionRatings['substructure']]))
sup.append(np.mean([int(j) for j in conditionRatings['superstructure']]))
deck.append(np.mean([int(j) for j in conditionRatings['deck']]))
dict_state_info['code'] = code
dict_state_info['name'] = name
dict_state_info['number of bridges'] = nob
dict_state_info['mean substructure'] = sub
dict_state_info['mean supertructure'] = sup
dict_state_info['mean deck'] = deck
In [52]:
dict_state_info
Out[52]:
In [53]:
pd.DataFrame(dict_state_info)
Out[53]:
In [ ]: